import pymysql
import os
import json
import math
import logging
logger = logging.getLogger('Yhlz')

if os.path.exists('./config/db_config.json'):
    with open('./config/db_config.json') as f:
        cfg = json.load(f)  # 模板 {"host": "119.29.170.200", "user": "root", "password": "117216", "database": "test"}
else:
    raise Exception('数据库配置文件不存在！')

def write(sql):
    # 打开数据库连接
    logger.debug(f"sql for write if : \n{sql}")
    db = pymysql.connect(host=cfg['host'],
                        user=cfg['user'],
                        password=cfg['password'],
                        database=cfg['database'])
    
    # 使用 cursor() 方法创建一个游标对象 cursor
    cursor = db.cursor()
    
    cursor.execute(sql)
    db.commit()
    
    # 关闭数据库连接
    db.close()


def write_signal(symbol, strategy_name, time, trade_df, algo=False, price=None):
    """将信号数据写入数据库"""
    trade_id = trade_df['trade_id'].to_list()
    trade_id_str = '['
    for s in trade_id:
        trade_id_str += '"'
        trade_id_str += s
        trade_id_str += '"'
        trade_id_str += ','
    trade_id_str = trade_id_str.strip(',')
    trade_id_str += ']'
    sql = f"""INSERT INTO strategy_signal (time, trade_id, symbol, strategy_name, algo, price) VALUES ({time}, '{trade_id_str}', '{symbol}', '{strategy_name}', {algo}, {price if price else 'NULL'});""" 
    write(sql)


def write_orders(orders):
    """
    将每日账户的委托数据写入数据库
    orders 是tqsdk api get_order 获取的一个dict的所有委托
    """
    sql = "REPLACE INTO orders (order_id,exchange_order_id,exchange_id,instrument_id,direction,offset,volume_orign,volume_left,limit_price,price_type,\
        volume_condition,time_condition,insert_date_time,last_msg,status,is_dead,is_online,is_error,trade_price) VALUES"
    if not orders:
        logger.debug('今天没有委托！')
        return
    for order in orders.values():
        sql += f""" ('{order.order_id}', '{order.exchange_order_id}', '{order.exchange_id}', '{order.instrument_id}', '{order.direction}', '{order.offset}', 
            {order.volume_orign}, {order.volume_left}, {order.limit_price}, '{order.price_type}', '{order.volume_condition}', '{order.time_condition}',
            {order.insert_date_time}, '{order.last_msg}', '{order.status}', {order.is_dead}, {order.is_online}, {order.is_error}, {order.trade_price if not math.isnan(order.trade_price) else 0}),"""
    sql = sql.strip(',')
    sql += ";"
    write(sql)


def write_trades(trades):
    """
    将每日成交数据写数据库
    通过tqapi 获取的包含一系列trade 的dict
    """
    sql = "REPLACE INTO trades (order_id,trade_id,exchange_trade_id,exchange_id,instrument_id,direction,offset,price,volume,trade_date_time) VALUES"
    if not trades:
        logger.debug('今天没有成交！')
        return
    for trade in trades.values():
        sql += f"""('{trade.order_id}', '{trade.trade_id}', '{trade.exchange_trade_id}', '{trade.exchange_id}', '{trade.instrument_id}', '{trade.direction}','{trade.offset}', {trade.price}, {trade.volume}, {trade.trade_date_time}),"""
    sql = sql.strip(',')
    sql += ";"
    write(sql)
